﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Security;

using System.Data;
using System.Configuration;
using System.IO;
using System.Reflection;
using System.Data.OleDb;
using System.Data.SqlClient;

using UltimusArchive;
using MyLib;
using Portal;
using Portal.Resources;
using ProcessControl.Logic;
using ProcessControl.Interface;

namespace BudgetSet
{
    public partial class BudgetMng : System.Web.UI.Page
    {
        public const string StrTableName = "PROC_BUDGETSET";
        public const string StrSortField = "BUDGETCODE";
        static public string CurFilter;
        static public string PaginateFilter;

        /// <summary>页面加载方法</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            //if (!IsPostBack)
            //{
            //    if (this.RadioExpense.Checked)
            //    {
            //        this.hf_Filter.Value = this.RadioExpense.Value;
            //    }
            //    else
            //    {
            //        this.hf_Filter.Value = this.RadioCapex.Value;
            //    }


            //    Page.ClientScript.RegisterStartupScript(Page.GetType(), "onload", "document.getElementById('divExpenseList').style.display='none'", true);
            //    Page.ClientScript.RegisterStartupScript(Page.GetType(), "onload", "document.getElementById('divCapexList').style.display='none'", true);
            //}
            //else
            //{
            //    if (this.hf_Filter.Value == "E")
            //    {
            //        Page.ClientScript.RegisterStartupScript(Page.GetType(), "onload", "document.getElementById('divExpenseList').style.display='block'", true);
            //        Page.ClientScript.RegisterStartupScript(Page.GetType(), "onload", "document.getElementById('divCapexList').style.display='none'", true);
            //    }
            //    {
            //        Page.ClientScript.RegisterStartupScript(Page.GetType(), "onload", "document.getElementById('divExpenseList').style.display='none'", true);
            //        Page.ClientScript.RegisterStartupScript(Page.GetType(), "onload", "document.getElementById('divCapexList').style.display='block'", true);
            //    }
            //}
           
        }

        /// <summary>点击查询按钮事件</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnQueryt_Click(object sender, EventArgs e)
        {
            //查询按钮，默认从第一页开始查，并且也返回记录总数以计算分页页数
            PaginateFilter = GetFilter();
            string SqlRecordCount = string.Format("select count(*) from {0} where 1=1 {1}", StrTableName, PaginateFilter);
            DataTable dtRecordCount = DataAccess.Instance("BizDB").ExecuteDataTable(SqlRecordCount);

            if (RadioExpense.Checked)
            {
                DataTable dt = Query(StrTableName, PaginateFilter, StrSortField, true, 1, AspNetPagerExpense.PageSize);
                AspNetPagerExpense.RecordCount = (int)dtRecordCount.Rows[0][0];
                AspNetPagerExpense.CurrentPageIndex = 1;
                rp_ExpenseList.DataSource = dt;
                rp_ExpenseList.DataBind();
            }
            else
            {
                DataTable dt = Query(StrTableName, PaginateFilter, StrSortField, true, 1, AspNetPagerCapex.PageSize);
                AspNetPagerCapex.RecordCount = (int)dtRecordCount.Rows[0][0];
                AspNetPagerCapex.CurrentPageIndex = 1;
                rp_CapexList.DataSource = dt;
                rp_CapexList.DataBind();
            }
        }

        /// <summary>点击导出按钮事件</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dtExportData = Query(StrTableName, PaginateFilter, StrSortField, false, 0, 0);
            string FileName = DateTime.Now.Year.ToString()
                            + DateTime.Now.Month.ToString()
                            + DateTime.Now.Day.ToString()
                            + DateTime.Now.Hour.ToString()
                            + DateTime.Now.Minute.ToString()
                            + DateTime.Now.Second.ToString()
                            + "_Budget_List.xls";
            Export(this.Page, dtExportData, FileName);

        }

        /// <summary>点击下载CapexBudget Excel模板按钮事件</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void DownloadCapexTemplate_Click(object sender, EventArgs e)
        {
            System.Web.HttpResponse httpResponse = this.Page.Response;
            string path = System.AppDomain.CurrentDomain.BaseDirectory;
            path = path + "\\Modules\\BudgetSet\\DataModule\\Module_CapexBudget.xls";
            DownFile(httpResponse, "Module_CapexBudget.xls", path);
        }

        /// <summary>点击下载ExpenseBudget Excel模板按钮事件</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void DownloadExpenseTemplate_Click(object sender, EventArgs e)
        {
            System.Web.HttpResponse httpResponse = this.Page.Response;
            string path = System.AppDomain.CurrentDomain.BaseDirectory;
            path = path + "\\Modules\\BudgetSet\\DataModule\\Module_ExpenseBudget.xls"; 
            DownFile(httpResponse, "Module_ExpenseBudget.xls", path);
        }

        #region  //Excel上传子方法
        /// <summary>
        /// 将Excel中的数据存储到本地DataSet中</summary>
        /// <param name="filenameurl"上传文件的路径></param>
        /// <param name="table">上传的文件名</param>
        public DataSet ExcelToDataSet(string filenameurl)
        {
            //string strConn_xls = string.Format("Provider=Microsoft.Jet.OleDb.4.0;" + "data source={0};Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'",filenameurl);
            string strConn_xlsx = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'", filenameurl);

            OleDbConnection conn = new OleDbConnection(strConn_xlsx);
            conn.Open();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            odda.Fill(ds, "Sheet1");
            return ds;
        }
        
        /// <summary>
        /// 检查数据集中的数据合法性</summary>
        /// <param name="dtData">要检查的数据集</param>
        public void CheckUpLoadFile(FileUpload fuExcel)
        {
            string fileName = fuExcel.PostedFile.FileName.ToString();
            string ContentType = fuExcel.PostedFile.ContentType;
            if ((fileName == "") || (ContentType != "application/octet-stream" && ContentType != "application/vnd.ms-excel"))
            {
                Response.Write("<script>alert('请选择标准的Excel文件！')</script>");
            }
        }

        /// <summary>
        /// 检查数据集中的数据合法性</summary>
        /// <param name="dtData">要检查的数据集</param>
        public Boolean CheckData(DataTable dtData)
        {
            string ColumnName;
            string BudgetCode = "";
            Boolean result = true;
            //当Excel表为空时,对用户进行提示
            if (dtData.Rows.Count == 0)
            {
                Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   
                result = false;
            }
            else
            {
                //当Excel表中必填字段为空时,对用户进行提示
                for (int i = 0; i < dtData.Rows.Count; i++)
                {                   
                    for (int j = 0; j < dtData.Columns.Count; j++)
                    {
                        ColumnName = dtData.Columns[j].ColumnName;
                        if ((dtData.Rows[i][j].ToString() == "")
                            && ((ColumnName == "STARTTIME") || (ColumnName == "STARTTIME") || (ColumnName == "BUDGETTYPE") || (ColumnName == "BUDGETCODE") || (ColumnName == "DEPARTMENT")
                            || (ColumnName == "ITEMSCODE") || (ColumnName == "ITEMSDESC") || (ColumnName == "COSTCENTER") || (ColumnName == "COSTCENTERNAME") || (ColumnName == "AMOUNT")))
                        {
                            Response.Write("<script>alert('模板中列" + ColumnName + "存在为空的记录，请检查后重新导入！')</script> ");
                            result = false;
                            break;
                        }
                    }
                    BudgetCode = "'" + dtData.Rows[i]["BUDGETCODE"].ToString() + "'," + BudgetCode;
                    if (!result)
                    {
                        break;
                    }
                }

                //当Excel表中BudgetCode与数据库里存在时，对用户进行提示
                BudgetCode = BudgetCode.Substring(0, BudgetCode.Length - 1);
                string SqlRecord = string.Format("select * from {0} where BUDGETCODE in ({1})", StrTableName, BudgetCode);
                DataTable dtRecord = DataAccess.Instance("BizDB").ExecuteDataTable(SqlRecord);
                if (dtRecord.Rows.Count > 0)
                {
                    string ErrBudgetCode = "";
                    for (int k = 0; k < dtRecord.Rows.Count; k++)
                    {
                        ErrBudgetCode = dtRecord.Rows[k]["BUDGETCODE"].ToString() + "," + ErrBudgetCode;
                    }
                    Response.Write("<script>alert('预算编号为：" + ErrBudgetCode + "的记录已经存在！')</script> ");
                    result = false;
                }
            }
            return result;
        }
        
        /// <summary>
        /// 拼接批量InsertSQL
        /// </summary>
        protected string GetBatchInsertSQL(DataTable dtData)
        {
            //拼接字段
            string StrFields = "";
            for (int i = 0; i < dtData.Columns.Count; i++)
            {
                StrFields = StrFields + dtData.Columns[i].ColumnName + ",";
            }
            StrFields = StrFields.Substring(0, StrFields.Length - 1);//去掉字符串尾部的逗号

            //拼接字段值
            string StrValues = "";
            string Sql = "";
            string ColumnName = "";
            for (int i = 0; i < dtData.Rows.Count; i++)
            {
                StrValues = "";
                for (int j = 0; j < dtData.Columns.Count; j++)
                {
                    ColumnName = dtData.Columns[j].ColumnName;
                    StrValues = StrValues + "'" + dtData.Rows[i][ColumnName].ToString() + "' ,";
                }
                StrValues = StrValues.Substring(0, StrValues.Length - 1);//去掉字符串尾部的逗号
                Sql = Sql + string.Format("insert into PROC_BUDGETSET ({0}) values({1});", StrFields, StrValues);
            }
            return Sql;
        }

        /// <summary>
        /// 将Excel保存到服务器上
        /// </summary>
        protected string SaveToServer(FileUpload fuFile)
        {
            string fileName = fuFile.PostedFile.FileName.ToString();
            string strFile = fuFile.PostedFile.FileName;
            int length = strFile.Length;
            int intID = strFile.LastIndexOf(".");
            int Len = strFile.LastIndexOf("\\") + 1;

            string OldDocName = strFile.Substring(Len, intID - Len);//原始文件名称
            string type = strFile.Substring(intID, length - intID);//文件类型 

            string y = System.DateTime.Now.Year.ToString();
            string m = System.DateTime.Now.Month.ToString();
            string d = System.DateTime.Now.Day.ToString();
            string h = System.DateTime.Now.Hour.ToString();
            string f = System.DateTime.Now.Minute.ToString();
            string s = System.DateTime.Now.Second.ToString();
            string docSuffixName = (y+ m + d +"-"+ h + f + s).ToString();

            string NewDocName = OldDocName + docSuffixName + type;//获得生成的文件名称

            string path = Request.PhysicalApplicationPath + "\\TempData\\" + NewDocName;
            string filepath = "\\TempData\\" + NewDocName;//文件保存路径   
            hf_FileName.Value = filepath;   //   文件保存路径临时存放在隐藏控件中   
            fuFile.PostedFile.SaveAs(path);//保存文件上传内容

            string source = Server.MapPath(Page.Request.ApplicationPath) + "TempData\\" + NewDocName;//完整的文件名
            return source;
        }
        #endregion

        /// <summary>点击导入按钮事件</summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                //检查上传的文件类型必须是Excel类型
                CheckUpLoadFile(Upfile);
                //将导入的Excel上传备份到服务器上
                string source = SaveToServer(Upfile);
                //将Excel中的数据存储到DataSet中
                DataSet ds = ExcelToDataSet(source);
                DataTable dt = ds.Tables["Sheet1"];
                //检查上传的数据是否符合条件
                if (CheckData(dt))
                {
                    //拼接插入脚本
                    string Sql = GetBatchInsertSQL(dt);
                    //插入数据库
                    DataAccess.Instance("BizDB").ExecuteDataTable(Sql);
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('批量导入成功！');", true);
                }
            }
            catch (Exception ee)
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('批量导入失败：" + ee.Message + "');", true);
            }
            finally
            {
            }
        }

        /// <summary>点击删除按钮事件</summary>
        /// 删除单行记录
        /// <param name="source"></param>
        /// <param name="e"></param>
        protected void btnDelete_Click(object sender, RepeaterCommandEventArgs e)
        {
            try
            {
                int ID = Convert.ToInt32(e.CommandName);
                string SqlDelete = string.Format("Delete from {0} where id = {1}", StrTableName, ID);
                DataAccess.Instance("BizDB").ExecuteDataTable(SqlDelete);
                Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + Portal.Resources.lang.SecurityList_DeleteSuccess + "!');", true);
                btnQueryt_Click(sender, e);
            }
            catch (Exception ex)
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + ex.Message + "!');", true);
            }
        }

        /// <summary>点击批量删除按钮事件</summary>
        /// 删除勾选的记录
        /// <param name="source"></param>
        /// <param name="e"></param>
        protected void btnBatchDelete_Click(object sender, EventArgs e)
        {        
            try
            {
                string checkbox;
                if (RadioExpense.Checked)
                {
                    checkbox = Request["cb_SelectE"];
                }
                else
                {
                    checkbox = Request["cb_SelectC"];
                }
                if (checkbox == null || checkbox == String.Empty)
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('请先选择要删除的记录!');", true);
                }
                else
                {
                    //String[] CheckboxArray = checkbox.Split(',');
                    //string SelectedID = "";
                    //for (int i = 0; i < CheckboxArray.Length; i++)
                    //{
                    //    SelectedID = SelectedID + "," + CheckboxArray[i];
                    //}
                    string SqlDelete = string.Format("Delete from {0} where id in ( {1})", StrTableName, checkbox);
                    DataAccess.Instance("BizDB").ExecuteDataTable(SqlDelete);
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + Portal.Resources.lang.SecurityList_DeleteSuccess + "!');", true);
                    btnQueryt_Click(sender, e);
                }
            }
            catch (Exception ex)
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + ex.Message + "!');", true);
            }
         } 

        /// <summary>翻页查询</summary>
        /// <param name="source"></param>
        /// <param name="e"></param>
        protected void AspNetPagerExpense_PageChanged(object sender, EventArgs e)
        {
            DataTable dt = Query(StrTableName, PaginateFilter, StrSortField, true, AspNetPagerExpense.CurrentPageIndex, AspNetPagerExpense.PageSize);
            rp_ExpenseList.DataSource = dt;
            rp_ExpenseList.DataBind();
        }

        /// <summary>翻页查询</summary>
        /// <param name="source"></param>
        /// <param name="e"></param>
        protected void AspNetPagerCapex_PageChanged(object sender, EventArgs e)
        {
            DataTable dt = Query(StrTableName, PaginateFilter, StrSortField, true, AspNetPagerCapex.CurrentPageIndex, AspNetPagerCapex.PageSize);
            rp_CapexList.DataSource = dt;
            rp_CapexList.DataBind();
        }     
        
        /// <summary>子方法：将DataTable中的数据导出到指定的Excel文件中</summary> 
        /// <param name="page">Web页面对象</param> 
        /// <param name="tab">包含被导出数据的DataTable对象</param> 
        /// <param name="FileName">Excel文件的名称</param> 
        public void Export(System.Web.UI.Page page, System.Data.DataTable tab, string FileName)
        {
            System.Web.HttpResponse httpResponse = page.Response;
            System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
            dataGrid.DataSource = tab.DefaultView;
            dataGrid.AllowPaging = false;
            dataGrid.HeaderStyle.BackColor = System.Drawing.Color.Green;
            dataGrid.HeaderStyle.ForeColor = System.Drawing.Color.White;
            dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            dataGrid.HeaderStyle.Font.Bold = true;
            dataGrid.DataBind();
            httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //filename="*.xls"; 
            httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); //GetEncoding("GB2312");
            httpResponse.ContentType = "application/ms-excel";
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            dataGrid.RenderControl(hw);

            string filePath = page.Server.MapPath("~/XLS/");
            try
            {
                DirectoryInfo p = new DirectoryInfo(filePath);
                if (!p.Exists)
                    p.Create();
            }
            catch
            {
            }
            filePath = filePath + FileName;
            System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
            sw.Write(tw.ToString());
            sw.Close();
            DownFile(httpResponse, FileName, filePath);
            httpResponse.End();
        }

        /// <summary> 子方法：将服务器上的模板文件下载到本地 </summary> 
        /// <param name="Response"></param> 
        /// <param name="fileName">文件名</param> 
        /// <param name="fullPath">Excel文件路径</param> 
        private bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)
        {
            try
            {
                Response.ContentType = "application/octet-stream";

                Response.AppendHeader("Content-Disposition", "attachment;filename=" +
                HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");
                System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
                long fLen = fs.Length;
                int size = 102400;//每100K同时下载数据 
                byte[] readData = new byte[size];//指定缓冲区的大小 
                if (size > fLen) size = Convert.ToInt32(fLen);
                long fPos = 0;
                bool isEnd = false;
                while (!isEnd)
                {
                    if ((fPos + size) > fLen)
                    {
                        size = Convert.ToInt32(fLen - fPos);
                        readData = new byte[size];
                        isEnd = true;
                    }
                    fs.Read(readData, 0, size);//读入一个压缩块 
                    Response.BinaryWrite(readData);
                    fPos += size;
                }
                fs.Close();
                //System.IO.File.Delete(fullPath);
                return true;
            }
            catch (Exception ee)
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('下载出错：" + ee.Message + "');", true);
                return false;
            }
        }

        /// <summary>子方法：查询数据</summary> 
        /// <param name="TableName">查询表名</param> 
        /// <param name="Filter">查询条件</param> 
        /// <param name="SortField">排序字段</param> 
        /// <param name="IsPagination">是否分页</param> 
        /// <param name="PageIndex">查询的当前页码</param> 
        /// <param name="PageSize">分页大小</param> 
        public DataTable Query(string TableName, string Filter, string SortField, Boolean IsPagination, int PageIndex, int PageSize)
        {
            string StrSqlResult;
            //如果分页
            if (IsPagination)
            {
                string StrMaxid = "0";
                string PreRecordCount = ((PageIndex - 1) * PageSize).ToString();
                //如果查询的不是首页，需要取得查询页之前排序字段最大值
                if (PageIndex > 1)
                {
                    string SqlPreMaxid = string.Format("select max ({0}) from (select top {1} {2} from {3} where 1=1 {4} order by {5}) t", SortField, PreRecordCount, SortField, TableName, Filter, SortField);
                    DataTable dt = DataAccess.Instance("BizDB").ExecuteDataTable(SqlPreMaxid);
                    StrMaxid = dt.Rows[0][0].ToString();
                }
                StrSqlResult = string.Format("select top {0} * from (select * from {1} where 1=1 {2}) t where t.{3} > '{4}' order by {5}", PageSize, TableName, Filter, SortField, StrMaxid, SortField);
            }
            //如果不分页
            else
            {
                StrSqlResult = string.Format("select * from {0} where 1=1 {1} order by {2}", TableName, Filter, SortField);
            }
            DataTable dtResult = DataAccess.Instance("BizDB").ExecuteDataTable(StrSqlResult);
            return dtResult;
        }

        /// <summary>子方法：拼接查询条件</summary> 
        /// 空 
        public string GetFilter()
        {
            SqlFilterUtil fb = new SqlFilterUtil();
            if (!string.IsNullOrEmpty(this.flt_BudgetCode.Text))
            {
                fb.AddLike("BUDGETCODE", this.flt_BudgetCode.Text.Trim());
            }
            if (!string.IsNullOrEmpty(this.flt_COMPANY.Text))
            {
                fb.AddLike("COMPANY", this.flt_COMPANY.Text.Trim());
            }
            if (!string.IsNullOrEmpty(this.flt_CostCenter.Text))
            {
                fb.AddLike("COSTCENTER", this.flt_CostCenter.Text.Trim());
            }
            if (!string.IsNullOrEmpty(this.flt_Department.Text))
            {
                fb.AddLike("DEPARTMENT", this.flt_Department.Text.Trim());
            }
            if (!string.IsNullOrEmpty(this.flt_STARTDATE.Text))
            {
                fb.AddScope("STARTDATE", "", this.flt_STARTDATE.Text.Trim());
            }
            if (!string.IsNullOrEmpty(this.flt_ENDDATE.Text))
            {
                fb.AddScope("ENDDATE", "", this.flt_ENDDATE.Text.Trim());
            }
            if (!string.IsNullOrEmpty(this.hf_Filter.Value))
            {
                fb.AddEqual("BUDGETTYPE", this.hf_Filter.Value.Trim());
            }
            if (!string.IsNullOrEmpty(this.fld_ITEMSCODE.Text))
            {
                fb.AddEqual("ITEMSCODE", this.fld_ITEMSCODE.Text.Trim());
            }
            //else
            //{
            //    Response.Write("预算类型必需选择！");
            //}
            return fb.ToString();
        }

        # region//测试接口方法

        public void Button1Click(object sender, EventArgs e)
        {
            BugetLogic Budget = new BugetLogic();
            DataTable dt = Budget.QueryBudgetByBudgetCode(flt_BudgetCode.Text);
            rp_ExpenseList.DataSource = dt;
            rp_ExpenseList.DataBind();
        }
        public void Button2Click(object sender, EventArgs e)
        {
            BugetLogic Budget = new BugetLogic();
            string errmsg;
            Budget.UpdateBalaceFromPR(flt_BudgetCode.Text, 500, out errmsg);
            flt_CostCenter.Text = errmsg;
        }
        public void Button3Click(object sender, EventArgs e)
        {
            BugetLogic Budget = new BugetLogic();
            string errmsg;
            Budget.UpdateBalaceFromPO(flt_BudgetCode.Text, 600, out errmsg);
            flt_CostCenter.Text = errmsg;
        }
        public void Button4Click(object sender, EventArgs e)
        {
            BugetLogic Budget = new BugetLogic();
            string errmsg;
            Budget.UpdateBalaceFromCAPEX(flt_BudgetCode.Text, 700, out errmsg);
            flt_CostCenter.Text = errmsg;
            //IBuget ibuget = ServiceContainer.Instance().GetService<IBuget>();
            //DataTable dt = ibuget.QueryBudgetByBudgetCode(flt_BudgetCode.Text);
        }
        # endregion
    }
}